package net.jforum.dao.generic;

import net.jforum.dao.SubForumDAO;
import net.jforum.entities.SubForum;
import net.jforum.entities.Forum;
import net.jforum.JForumExecutionContext;
import net.jforum.exceptions.DatabaseException;
import net.jforum.util.preferences.SystemGlobals;
import net.jforum.util.DbUtils;

import java.util.List;
import java.util.ArrayList;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Created by IntelliJ IDEA.
 * User: stzhang
 * Date: 2010-2-10
 * Time: 16:41:24
 * To change this template use File | Settings | File Templates.
 */
public class GenericSubForumDAO extends AutoKeys implements SubForumDAO {

    public List selectAll() {
        PreparedStatement p = null;
		ResultSet rs = null;
		try {
			p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("SubForumModel.selectAll"));
			rs = p.executeQuery();
			List l = new ArrayList();
			while (rs.next()) {
				l.add(this.fillSubForum(rs));
			}
			return l;
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(rs, p);
		}
    }

    public List selectAllByForumId(int forumId) {
        PreparedStatement p = null;
		ResultSet rs = null;
		try {
			p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("SubForumModel.selectAllByForumId"));
            p.setInt(1, forumId);
			rs = p.executeQuery();
			List l = new ArrayList();
			while (rs.next()) {
				l.add(this.fillSubForum(rs));
			}
			return l;
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(rs, p);
		}
    }

    public List selectAllByForumCategoryId(int categoryId) {
      PreparedStatement p = null;
		ResultSet rs = null;
		try {
			p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("SubForumModel.selectAllByCategoryId"));
            p.setInt(1, categoryId);
			rs = p.executeQuery();
			List l = new ArrayList();
			while (rs.next()) {
				l.add(this.fillSubForum(rs));
			}
			return l;
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(rs, p);
		}
    }
    //SubForumModel.findSubforumById
    public SubForum findSubforumById(int forumId,int subforumId){
    	PreparedStatement p = null;
		ResultSet rs = null;
		try {
			p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("SubForumModel.findSubforumById"));
            p.setInt(1, forumId);
            p.setInt(2, subforumId);
			rs = p.executeQuery();
			SubForum sf = null;
			if(rs.next()) {
				sf = this.fillSubForum(rs);
			}
			return sf;
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(rs, p);
		}
    	
    }
    
    public int addNew(SubForum subforum)
	{
		// Gets the higher order
		PreparedStatement pSubform = null;
		ResultSet rs = null;
		try {
			pSubform = this.getStatementForAutoKeys("SubForumModel.addNew");
			pSubform.setInt(1, subforum.getCategoryId());
			pSubform.setInt(2, subforum.getForumId());
			pSubform.setString(3, subforum.getName());
			pSubform.setString(4, subforum.getDescription());
			pSubform.setInt(5, subforum.getOrder());
			pSubform.setInt(6, subforum.isModerated() ? 1 : 0);

			this.setAutoGeneratedKeysQuery(SystemGlobals.getSql("ForumModel.lastGeneratedForumId"));
			int subforumId = this.executeAutoKeysQuery(pSubform);
			subforum.setId(subforumId);
			return subforumId;
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(rs, pSubform);
		}
	}
    
    public void update(SubForum subforum)
	{
		PreparedStatement p = null;
		try {
			p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("SubForumModel.update"));
			p.setString(1, subforum.getName());
			p.setString(2, subforum.getDescription());
			p.setInt(3, subforum.isModerated() ? 1 : 0);
			p.setInt(4, subforum.getId());

			p.executeUpdate();
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(p);
		}
	}
    
    public void remove(int forumId, int subforumId){
    	PreparedStatement p = null;
		try {
			p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("SubForumModel.delete"));
			p.setInt(1, forumId);
			p.setInt(2, subforumId);
			p.executeUpdate();
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(p);
		}
    	
    }
    
    protected SubForum fillSubForum(ResultSet rs) throws SQLException{
        SubForum sf = new  SubForum();
        sf.setId(rs.getInt("subforum_id"));
		sf.setCategoryId(rs.getInt("categories_id"));
		sf.setName(rs.getString("forum_name"));
		sf.setDescription(rs.getString("forum_desc"));
		sf.setOrder(rs.getInt("forum_order"));
		sf.setTotalTopics(rs.getInt("forum_topics"));
		sf.setLastPostId(rs.getInt("forum_last_post_id"));
		sf.setModerated(rs.getInt("moderated") > 0);
		sf.setTotalPosts(this.countForumPosts(sf.getId()));
        sf.setForumId(rs.getInt("forum_id"));
        return sf;
    }

    /**
     * 获取子论坛的总回复数
     * @param subForumId
     * @return
     */
    protected int countForumPosts(int subForumId)
	{
		PreparedStatement p = null;
		ResultSet rs = null;
		try {
			p = JForumExecutionContext.getConnection().prepareStatement(
					SystemGlobals.getSql("SubForumModel.countSubFormPost"));
			p.setInt(1, subForumId);
			rs = p.executeQuery();

			if (rs.next()) {
				return rs.getInt(1);
			}
			return 0;
		}
		catch (SQLException e) {
			throw new DatabaseException(e);
		}
		finally {
			DbUtils.close(rs, p);
		}
	}
    
}
